USE [Comercial]
GO

drop table [dbo].[Lotes]

/****** Object:  Table [dbo].[Lotes]    Script Date: 01/20/2011 09:57:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Lotes](
	[Id_Rubro] [int] NOT NULL,
	[Id_SubRubro] [int] NOT NULL,
	[Id_Articulo] [int] NOT NULL,
	[Articulo] [nvarchar](50) NOT NULL,
	[Lote] [nvarchar](30) NOT NULL,
	[Estado] [int] NOT NULL,
	[FechaVto] [datetime] NOT NULL,
	[Saldo] [decimal](12, 2) NOT NULL,
	[Id_Usuario] [int] NOT NULL,
 CONSTRAINT [PK_Lotes] PRIMARY KEY CLUSTERED 
(
	[Id_Rubro] ASC,
	[Id_SubRubro] ASC,
	[Id_Articulo] ASC,
	[Lote] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/cbendayan
-- Creacion:		27/07/2008
-- Descripcion: 	GetOne Lote por Id_Articulo
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[Lotes_GetOne]
		@Id_Rubro			int,
		@Id_SubRubro		int,			
		@Id_Articulo		int,
		@Lote				nvarchar(30)
AS
BEGIN
	SET NOCOUNT ON
	SET DATEFORMAT DMY;
SELECT 
		Id_Rubro,
		Id_SubRubro,			
		Id_Articulo,
		Articulo,
		Lote,
		Estado,
		FechaVto,
		Saldo,
		Id_Usuario

	FROM Lotes 
	WHERE 
			Id_Rubro	= @Id_Rubro		AND 
			Id_SubRubro	= @Id_SubRubro	AND  
			Id_Articulo	= @Id_Articulo	AND
			Lote		= @Lote	
END

-- exec Lotes_GetOne 1,1,1,'1'


GO
 

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/CBendayan
-- Creacion:		27/07/2008
-- Descripcion: 	INSERT en tabla Lotes
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[Lotes_Insert]

	-- Lista de Parmetros
		@Id_Rubro		int,
		@Id_SubRubro	int,			
		@Id_Articulo	int,
		@Articulo		nvarchar(50),
		@Lote			nvarchar(30),
		@Estado			int,
		@FechaVto		datetime,
		@Saldo			decimal(12,2),
		@Id_Usuario		int
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO Lotes (
		Id_Rubro,
		Id_SubRubro,			
		Id_Articulo,
		Articulo,
		Lote,
		Estado,
		FechaVto,
		Saldo,
		Id_Usuario

	) VALUES (
		@Id_Rubro,
		@Id_SubRubro,			
		@Id_Articulo,
		@Articulo,
		@Lote,
		@Estado,
		@FechaVto,
		@Saldo,
		@Id_Usuario
	);
END

GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/CBendayan
-- Creacion:		05/10/2008
-- Descripcion: 	UPDATE en tabla Lotes
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[Lotes_Update]

	-- Lista de Parmetros
		@Id_Rubro		int,
		@Id_SubRubro	int,			
		@Id_Articulo	int,
		--@Articulo		nvarchar(50),
		@Lote			nvarchar(30),
		@Estado			int,
		@FechaVto		datetime,
		@Saldo			decimal(12,2),
		@Id_Usuario		int
AS
SET NOCOUNT ON

UPDATE Lotes 
	SET 
		Estado		= @Estado,
		FechaVto	= @FechaVto,
		Saldo		= @Saldo,
		Id_Usuario	= @Id_Usuario
WHERE
		Id_Rubro	= @Id_Rubro		AND 
		Id_SubRubro	= @Id_SubRubro	AND  
		Id_Articulo	= @Id_Articulo	AND
		Lote		= @Lote



GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/CBendayan
-- Creacion:		25/03/2010
-- Descripcion: 	UPDATE Saldo en tabla Lotes
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[Lotes_UpdateSaldo]

	-- Lista de Parmetros
		@Id_Rubro		int,
		@Id_SubRubro	int,			
		@Id_Articulo	int,
		@Lote			nvarchar(30),
		@Saldo			decimal(12,2)
AS
SET NOCOUNT ON

UPDATE Lotes 
	SET 
		Saldo		= @Saldo
WHERE
		Id_Rubro	= @Id_Rubro		AND 
		Id_SubRubro	= @Id_SubRubro	AND  
		Id_Articulo	= @Id_Articulo	AND
		Lote		= @Lote
		
		
		
GO


--------------------------------------------------------------------------------------------
-- Autor:			WeCo\cbendayan
-- Creacion:		22/03/2010
-- Descripcion: 	Search en tabla Lotes
-------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[Lotes_Search]
	-- Lista de Parmetros
	@Id_Rubro			int = null,
	@Id_SubRubro		int = null,			
	@Id_Articulo		int = null,
	@Lote				nvarchar(30) = null,
	@Estado				int = null,
	@FechaVto			datetime = null
	

AS
BEGIN
	SET NOCOUNT ON
	SET DATEFORMAT DMY

	----------------------------------------
	-- Definimos Variables
	----------------------------------------
	DECLARE @sql        nvarchar(4000)
	DECLARE @parametros nvarchar(4000)

	SET @sql = N'	SELECT 
					Id_Rubro,
					Id_SubRubro,			
					Id_Articulo,
					Articulo,
					Lote,
					Estado,
					FechaVto,
					Saldo,
					Id_Usuario
					FROM Lotes WITH (NOLOCK) WHERE 1 = 1 '

	IF (@Id_Rubro IS NOT NULL)
	BEGIN			
			SET @sql = @sql + ' AND Id_Rubro = @Id_Rubro '
	END

	IF (@Id_SubRubro IS NOT NULL)
	BEGIN			
			SET @sql = @sql + ' AND Id_SubRubro = @Id_SubRubro'
	END

	IF (@Id_Articulo IS NOT NULL)
	BEGIN			
			SET @sql = @sql + ' AND Id_Articulo = @Id_Articulo '
	END

	IF (@Lote IS NOT NULL)
	BEGIN			
			SET @sql = @sql + ' AND Lote = @Lote '
	END

	IF (@Estado IS NOT NULL)
	BEGIN			
			SET @sql = @sql + ' AND Estado = @Estado '
	END

	IF (@FechaVto IS NOT NULL)	
	BEGIN
			SET @sql = @sql + ' AND FechaVto > @FechaVto '
	END

	BEGIN
			SET @sql = @sql + ' ORDER BY Id_Rubro,Id_SubRubro,Id_Articulo,Lote '
	END
	
	SELECT @parametros =	'@Id_Rubro			int,
							@Id_SubRubro		int,			
							@Id_Articulo		int,
							@Lote				nvarchar(30),
							@Estado				int,
							@FechaVto			datetime '

	EXEC sp_executesql @sql, @parametros,
							@Id_Rubro,
							@Id_SubRubro,			
							@Id_Articulo,
							@Lote,
							@Estado,
							@FechaVto 
END

-- exec Lotes_Search 1,1,20,null,null,null




GO


alter table dbo.ComprasDet
alter column Lote nvarchar(30) not null 

GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/CBendayan
-- Creacion:		27/07/2008
-- Descripcion: 	INSERT en tabla ComprasDet
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[ComprasDet_Insert]

	-- Lista de Parmetros
		@Id_Compras     float,
		@Item			int,
		@Id_Rubro		int,
		@Rubro			nvarchar(50),
		@Id_SubRubro	int,
		@SubRubro		nvarchar(50),
		@Id_Articulo	int,
		@Articulo		nvarchar(50),
		@Lote			nvarchar(30),
		@Kilos			decimal(12,2),
		@Precio			decimal(12,2),
		@Monto			decimal(12,2),
		@TTasa			int,
		@Tasa			decimal(5,3),
		@Impuesto		decimal(18,2),
		@Total			decimal(18,2),
		@Id_Usuario		int

AS
BEGIN
	SET NOCOUNT ON

	SET DATEFORMAT DMY

	INSERT INTO ComprasDet (
		Id_Compras,
		Item,
		Id_Rubro,
		Rubro,
		Id_SubRubro,
		SubRubro,
		Id_Articulo,
		Articulo,
		Lote,
		Kilos,
		Precio,
		Monto,
		TTasa,
		Tasa,
		Impuesto,
		Total,
		Id_Usuario 

	) VALUES (
		@Id_Compras,
		@Item,
		@Id_Rubro,
		@Rubro,
		@Id_SubRubro,
		@SubRubro,
		@Id_Articulo,
		@Articulo,
		@Lote,
		@Kilos,
		@Precio,
		@Monto,
		@TTasa,
		@Tasa,
		@Impuesto,
		@Total,
		@Id_Usuario
	);
END


GO



alter table dbo.VentasDet
alter column Lote nvarchar(30) not null 


GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/CBendayan
-- Creacion:		27/07/2008
-- Descripcion: 	INSERT en tabla VentasDet
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[VentasDet_Insert]

	-- Lista de Parmetros
		@Id_Ventas		float,
		@Item			int,
		@Id_Rubro		int,
		@Rubro			nvarchar(50),
		@Id_SubRubro	int,
		@SubRubro		nvarchar(50),
		@Id_Articulo	int,
		@Articulo		nvarchar(50),
		@Lote			nvarchar(30),
		@Kilos			decimal(18,2),
		@Precio			decimal(18,2),
		@Monto			decimal(18,2),
		@TTasa			int,
		@Tasa			decimal(5,3),
		@Impuesto		decimal(18,2),
		@Total			decimal(18,2),
		@Id_Usuario		int,
		@FechaVto		datetime 

AS
BEGIN
	SET NOCOUNT ON

	SET DATEFORMAT DMY

	INSERT INTO VentasDet (
		Id_Ventas,
		Item,
		Id_Rubro,
		Rubro,
		Id_SubRubro,
		SubRubro,
		Id_Articulo,
		Articulo,
		Lote,
		Kilos,
		Precio,
		Monto,
		TTasa,
		Tasa,
		Impuesto,
		Total,
		Id_Usuario,
		FechaVto  

	) VALUES (
		@Id_Ventas,
		@Item,
		@Id_Rubro,
		@Rubro,
		@Id_SubRubro,
		@SubRubro,
		@Id_Articulo,
		@Articulo,
		@Lote,
		@Kilos,
		@Precio,
		@Monto,
		@TTasa,
		@Tasa,
		@Impuesto,
		@Total,
		@Id_Usuario,
		@FechaVto
	);
END


GO

alter table dbo.RemitosDet
alter column Lote nvarchar(30)


GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/cbendayan
-- Creacion:		19/04/2010
-- Descripcion: 	GetAll RemitosDet de un Id_Remitos
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[RemitosDet_GetAllByLote]
		@Id_Rubro		int,
		@Id_SubRubro	int,
		@Id_Articulo	int,
		@Lote			nvarchar(30)
AS
BEGIN
	SET NOCOUNT ON
	SET DATEFORMAT DMY;
SELECT 
		Id_Remitos,
		Item,
		Id_Rubro,
		Rubro,
		Id_SubRubro,
		SubRubro,
		Id_Articulo,
		Articulo,
		Lote,
		FechaVto,
		Kilos,
		Id_Usuario 

	FROM RemitosDet 
	WHERE 
		Id_Rubro		=	@Id_Rubro		AND
		Id_SubRubro		=	@Id_SubRubro	AND
		Id_Articulo		=	@Id_Articulo	AND
		Lote			=	@Lote 
	ORDER BY
		Id_Rubro,
		Id_SubRubro,
		Id_Articulo,
		Lote,
		Item 	
END

-- exec RemitosDet_GetAll 1,1,20,'7'


GO


--------------------------------------------------------------------------------------------
-- Autor:			WeCo/CBendayan
-- Creacion:		19/04/2010
-- Descripcion: 	INSERT en tabla RemitosDet
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[RemitosDet_Insert]

	-- Lista de Parmetros
		@Id_Remitos		float,
		@Item			int,
		@Id_Rubro		int,
		@Rubro			nvarchar(50),
		@Id_SubRubro	int,
		@SubRubro		nvarchar(50),
		@Id_Articulo	int,
		@Articulo		nvarchar(50),
		@Lote			nvarchar(30),
		@FechaVto		datetime,
		@Kilos			decimal(18,2),
		@Id_Usuario		int

AS
BEGIN
	SET NOCOUNT ON

	SET DATEFORMAT DMY

	INSERT INTO RemitosDet (
		Id_Remitos,
		Item,
		Id_Rubro,
		Rubro,
		Id_SubRubro,
		SubRubro,
		Id_Articulo,
		Articulo,
		Lote,
		FechaVto,
		Kilos,
		Id_Usuario 

	) VALUES (
		@Id_Remitos,
		@Item,
		@Id_Rubro,
		@Rubro,
		@Id_SubRubro,
		@SubRubro,
		@Id_Articulo,
		@Articulo,
		@Lote,
		@FechaVto,
		@Kilos,
		@Id_Usuario
	);
END


GO

drop table [dbo].[MovStock]


GO


CREATE TABLE [dbo].[MovStock](
	[Id_Rubro] [int] NOT NULL,
	[Rubro] [nvarchar](50) NOT NULL,
	[Id_SubRubro] [int] NOT NULL,
	[SubRubro] [nvarchar](50) NOT NULL,
	[Id_Articulo] [int] NOT NULL,
	[Articulo] [nvarchar](50) NOT NULL,
	[Lote] [nvarchar](30) NOT NULL,
	[Id_Mov] [float] NOT NULL,
	[Operacion] [int] NOT NULL,
	[Id_Operacion] [int] NOT NULL,
	[Fecha] [datetime] NOT NULL,
	[Kilos] [decimal](12, 2) NOT NULL,
	[Saldo] [decimal](12, 2) NOT NULL,
	[ALote] [int] NOT NULL,
	[Id_Usuario] [int] NOT NULL,
 CONSTRAINT [PK_MovStock_1] PRIMARY KEY CLUSTERED 
(
	[Id_Rubro] ASC,
	[Id_SubRubro] ASC,
	[Id_Articulo] ASC,
	[Lote] ASC,
	[Id_Mov] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


--------------------------------------------------------------------------------------------
-- Autor:			WeCo/CBendayan
-- Creacion:		04/11/2008
-- Descripcion: 	INSERT en tabla MovStock
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[MovStock_Insert]

	-- Lista de Parmetros
		@Id_Rubro		int,
		@Rubro			nvarchar(50),
		@Id_SubRubro	int,
		@SubRubro		nvarchar(50),
		@Id_Articulo    int,
		@Articulo		nvarchar(50),
		@Lote			nvarchar(30),
		@Id_Mov			float,
		@Operacion		int,
		@Id_Operacion	int,
		@Fecha			datetime,
		@Kilos			decimal(12,2),
		@Saldo			decimal(12,2),
		@ALote	 		int,
		@Id_Usuario		int
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO MovStock (
		Id_Rubro,
		Rubro,
		Id_SubRubro,
		SubRubro,
		Id_Articulo,
		Articulo,
		Lote,
		Id_Mov,
		Operacion,
		Id_Operacion,
		Fecha,
		Kilos,
		Saldo,
		ALote,
		Id_Usuario

	) VALUES (
		@Id_Rubro,
		@Rubro,
		@Id_SubRubro,
		@SubRubro,
		@Id_Articulo,
		@Articulo,
		@Lote,
		@Id_Mov,
		@Operacion,
		@Id_Operacion,
		@Fecha,
		@Kilos,
		@Saldo,
		@ALote,
		@Id_Usuario

	);
END


GO


--------------------------------------------------------------------------------------------
-- Autor:			WeCo\cbendayan
-- Creacion:		18/03/2009
-- Descripcion: 	Proximo Id en MovSotck
-------------------------------------------------------------------------------------------

ALTER procedure [dbo].[MovStock_ProximoID]

	@Id_Rubro		int,
	@Id_SubRubro	int,
	@Id_Articulo	int,
	@Lote			nvarchar(30)

as
SELECT isnull(max(Id_Mov),0)+1 as ProxId_Mov 
FROM MovStock
WHERE 
	Id_Rubro	= @Id_Rubro		AND
	Id_SubRubro	= @Id_SubRubro	AND 
	Id_Articulo	= @Id_Articulo	AND
	Lote		= @Lote

-- exec MovStock_ProximoID 1,1,1,"1"



GO







